package com.newhero.product.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class ExcelUtil {

	public static void main(String args[]) throws FileNotFoundException, IOException {
        List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
        Map<String, Object> dataMap = new HashMap<String, Object>();
        dataMap.put("A", "1");
        dataMap.put("B", "2");
        dataMap.put("C", "3");
        dataList.add(dataMap);
        dataList.add(dataMap);
        dataList.add(dataMap);
        dataList.add(dataMap);
        HSSFWorkbook excel = generateExcel(dataList, "D:/1.xls", 0, 0, 0, 3);
        excel.write(new FileOutputStream("D:/2.xls"));
    }

    // 读写xls和xlsx格式时，HSSFWorkbook针对xls，XSSFWorkbook针对xlsx
    public static HSSFWorkbook generateExcel(List<Map<String, Object>> dataList, String template,// 模板路径
        int numSheet,// 第几个工作簿
        int startRow,// 开始行号，即记录变量的行号。起始为0
        int xStart, int xEnd) {
        InputStream is = null;
        HSSFWorkbook excel = null;
        try {
            is = new FileInputStream(template);
            excel = new HSSFWorkbook(is);
            HSSFSheet xssfSheet = excel.getSheetAt(numSheet);
            HSSFRow row = xssfSheet.getRow(startRow);

            for (int i = 0; i < dataList.size(); i++) {
                Map<String, Object> data = dataList.get(i);
                HSSFRow tempRow = xssfSheet.createRow((short) (startRow + i + 1));
                if (row.getRowStyle() != null)
                    tempRow.setRowStyle(row.getRowStyle());
                for (int j = xStart; j < xEnd; j++) {
                    if (row.getCell(j) != null) {
                        HSSFCell cell = row.getCell(j);
                        String key = cell.getStringCellValue();
                        //key = key.substring(2, key.length() - 1);
                        if (data.containsKey(key)) {
                            String value = "";
                            if (data.get(key) != null && !"".equals(key)) {
                                value = data.get(key).toString();
                            }
                            HSSFCell c = tempRow.createCell((short) j);
                            HSSFCellStyle cellStyle= cell.getCellStyle();    
                            cellStyle.setWrapText(true);    
                            c.setCellStyle(cellStyle); 
                            c.setCellValue(new HSSFRichTextString(value));
                        }
                    }
                }
            }
            xssfSheet.shiftRows(startRow + 1, xssfSheet.getLastRowNum(), -1);
            // HSSFRow lastRow = xssfSheet.getRow(xssfSheet.getLastRowNum());
            // for (int j = xStart; j < xEnd; j++) {
            // HSSFCell cell = lastRow.getCell(j);
            // cell.setCellStyle(null);
            // }
            xssfSheet.removeRow(xssfSheet.getRow(xssfSheet.getLastRowNum() + 1));
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return excel;
    }

    public static HSSFWorkbook generateExcel(List dataList, String filePath,
        Map<String, Object> dataMap) {
        // TODO Auto-generated method stub
        InputStream is = null;
        HSSFWorkbook excel = null;
        try {
            is = new FileInputStream(filePath);
            excel = new HSSFWorkbook(is);
            HSSFSheet xssfSheet = excel.getSheetAt(0);
            int rownum = 0;
            int lastRow = xssfSheet.getLastRowNum();
            for (int row = 0; row < lastRow; row++) {
                HSSFRow rowExcl = xssfSheet.getRow(row);
                for (int i = 0; i < rowExcl.getLastCellNum(); i++) {
                    HSSFCell cell = rowExcl.getCell(i);
                    String key ="";
                    try{
                    key = cell.getStringCellValue();
                    }catch (Exception e) {
                        // TODO: handle exception
                    }
                    if (!"".equals(key) && key != null) {
                        if (key.indexOf("&[") !=-1) {
                            key = key.substring(2, key.length() - 1);
                            String value = "";
                            if (dataMap.get(key) != null && !"".equals(key)) {
                                value = dataMap.get(key).toString();
                            }
                            cell.setCellValue(value);
                        }
                        if (key.indexOf("&{") !=-1) {
                            rownum = row;
                            break;
                        }
                    }
                }

            }
            if (rownum != 0 && dataList.size() > 0) {
                HSSFRow row = xssfSheet.getRow(rownum);
                for (int i = 0; i < dataList.size(); i++) {
                    Map<String, Object> data = (Map<String, Object>) dataList.get(i);
                    HSSFRow tempRow = xssfSheet.createRow((short) (rownum + i));
                    if (row.getRowStyle() != null)
                        tempRow.setRowStyle(row.getRowStyle());
                    for (int j = 0; j < 30; j++) {
                        if (row.getCell(j) != null) {
                            HSSFCell cell = row.getCell(j);
                            String key ="";
                            try{
                            key = cell.getStringCellValue();
                            key = key.substring(2, key.length() - 1);
                            }catch (Exception e) {
                                // TODO: handle exception
                            }
                            if (data.containsKey(key)&&!"".equals(key)) {
                                String value = "";
                                if (data.get(key) != null && !"".equals(key)) {
                                    value = data.get(key).toString();
                                }
                                HSSFCell c = tempRow.createCell((short) j);
                                c.setCellStyle(cell.getCellStyle());
                                c.setCellValue(value);
                            }
                        }
                    }
                }
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return excel;
    }
}
